package com.g2room.driving.generate.qgsh;

import java.awt.image.BufferedImage;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.OutputStreamWriter;
import java.io.StringWriter;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import javax.imageio.ImageIO;

import org.jsoup.Jsoup;

import com.g2room.driving.generate.to.ArticleTo;
import com.g2room.driving.model.po.Category;
import com.g2room.driving.util.Constants;
import com.g2room.driving.util.DbUtil;
import com.g2room.driving.util.HtmlUtil;
import com.g2room.driving.util.ImageUtil;

import freemarker.template.Configuration;
import freemarker.template.Template;

public class ReleaseArticle {
	private static String basePath = Constants.basePath + "qgsh/";
	private static String baseHtmlPath = Constants.baseHtmlPath + "qgsh/";
	private static String localImgDir = Constants.localImgDir + "qgsh/targetImg/";
	private static String remoteImgDir = "http://img.1nsh.com/";
	public static String publishHtmlPath = null;
	static Map<String, String> oldImgMap = getOldImgs(0);
	private static Map<Long, Category> cats = new HashMap<Long, Category>();
	static int outFlag = -1;
	static String name = "1nsh";
	
	public static void createHtml() {
		 @SuppressWarnings("deprecation")
		 Configuration cfg = new Configuration();
		 try {
			 int updateId = 4;
			 String publishTime = DbUtil.getPublishTimeStr(updateId);
			 cfg.setDirectoryForTemplateLoading(new File(basePath));
			 System.out.println(name + "生成主页！");
			 createIndex(cfg);
			 
			 System.out.println(name + "生成最新最热页！");
			 createLatest(cfg);
			 System.out.println(name + "生成列表页！");
			 createList(cfg, publishTime);
			 
			 System.out.println(name + "生成详情页！");
			 createDetail(cfg, publishTime);
			 DbUtil.updateTime(updateId);
				// 关闭连接
				DbUtil.closeConn();
		} catch (IOException e) {
			System.out.println(name + "发生异常：" + e.getMessage());
			e.printStackTrace();
		}  
	}
	public static void createIndex(Configuration cfg) {
		try {
			Map<String, Object> articleMap = new HashMap<String, Object>();
			// 获取幻灯片，返回5条数据
			List<ArticleTo> artis = getArticlesImgByCategoryId(null, 0, 400, 1.47, 0.4, 5, true);
			articleMap.put("slides", artis);
			String ids = getIds(null, artis);
			// 获取最新11条数据
			List<ArticleTo> artisSticks = getArticles(ids, 0, 11, false, false, false);
			ids = getIds(ids, artisSticks);
			articleMap.put("latest", artisSticks);
			
			// 获取一楼图片，7张图，取所有缩略图的
			List<ArticleTo> thumbArtis = getArticles(ids, 0, 7, false, true, false);
			ids = getIds(ids, thumbArtis);
			articleMap.put("thumbArtis", thumbArtis);
			
			// TODO 获取两性话题最新图，285-200
			List<ArticleTo> sexImgArtis = getArticlesImgByCategoryId(ids, 1, 200, 1.43, 0.5, 1, false);
			ids = getIds(ids, sexImgArtis);
			articleMap.put("sexImgArtis", sexImgArtis);
			// 获取两性话题、婚姻生活、恋爱物语列表，各10条记录
			List<ArticleTo> sexArtis = getArticles(ids, 1, 5, false, false, true);
			articleMap.put("sexArtis", sexArtis);
			List<ArticleTo> marryArtis = getArticles(ids, 3, 5, false, false, true);
			articleMap.put("marryArtis", marryArtis);
			List<ArticleTo> loveArtis = getArticles(ids, 6, 5, false, false, true);
			articleMap.put("loveArtis", loveArtis);
			
			// 口述实录，图2张，列表数据10条，分两组展示
			List<ArticleTo> oralImgArtis = getArticlesImgByCategoryId(ids, 2, 110, 1.5, 0.5, 2, false);
			ids = getIds(ids, oralImgArtis);
			articleMap.put("oralImgArtis", oralImgArtis);
			List<ArticleTo> oralArtis = getArticles(ids, 2, 20, false, false, false);
			articleMap.put("oralArtis", oralArtis);
			// 情感倾诉，图一张，列表数据10条。
			List<ArticleTo> feelImgArtis = getArticlesImgByCategoryId(ids, 4, 250, 1.68, 0.5, 1, false);
			ids = getIds(ids, feelImgArtis);
			articleMap.put("feelImgArtis", feelImgArtis);
			List<ArticleTo> feelArtis = getArticles(ids, 4, 15, false, false, false);
			articleMap.put("feelArtis", feelArtis);
			
			// 婆媳关系、名人情事、单身列表数据各10条
			List<ArticleTo> motherArtis = getArticles(ids, 5, 13, false, false, false);
			articleMap.put("motherArtis", motherArtis);
			List<ArticleTo> celebritiesArtis = getArticles(ids, 7, 12, false, false, false);
			articleMap.put("celebritiesArtis", celebritiesArtis);
			List<ArticleTo> singleArtis = getArticles(ids, 8, 12, false, false, false);
			articleMap.put("singleArtis", singleArtis);
			// 美图欣赏，图6张，单独获取
			articleMap.put("imges", getImages(ids));
			
			/**
			 * 1	两性话题   2	口述实录  3	婚姻生活  4	情感倾诉  5	婆媳关系  6	恋爱物语  7	名人情事  8	单身男女
			 */
			Template tmpl = null;
			StringWriter out = null;
			try {
				System.out.println(name + "加载模板文件。");
				tmpl = cfg.getTemplate("index.ftl");
				out = new StringWriter();
				tmpl.process(articleMap, out);
				System.out.println(name + "本次生成文件路径："+HtmlUtil.getPath(baseHtmlPath));
				// 生成Mapper文件
				createFile(out.toString(), "index.html");
			} catch (Exception e) {
				System.out.println(name + "生成主页页面异常：" + e.getMessage());
				e.printStackTrace();
			} finally {
				try {
					if (null != out)
						out.close();
				} catch (Exception e) {
					e.printStackTrace();
				}
			}
		} catch (Exception e) {
			System.out.println(name + "生成主页异常：" + e.getMessage());
			e.printStackTrace();
		}
	}
	
	public static void createLatest(Configuration cfg) {
		try {
			Map<String, List<ArticleTo>> articleMap = new HashMap<String, List<ArticleTo>>();
			// 获取全局最新文章
			articleMap.put("latestArticles", getLatestArtis(10));
			articleMap.put("hotArticles", getArticles(null, 0, 10, true, true, false));
			StringWriter out = null;
			try {
				Template tmpl = cfg.getTemplate("latest.ftl");
				out = new StringWriter();
				tmpl.process(articleMap, out);
				// 生成文件
				createFile(out.toString(), "latest.html");
			} catch (Exception e) {
				System.out.println(name + "发生异常：" + e.getMessage());
				e.printStackTrace();
			} finally {
				try {
					if (null != out)
						out.close();
				} catch (Exception e) {
					e.printStackTrace();
				}
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
	}
	public static void createDetail(Configuration cfg, String publishTime) {
		try {
			if(null == cats || cats.isEmpty()) getTypes();
			Map<String, List<ArticleTo>> articlesMap = new HashMap<String, List<ArticleTo>>();
			List<ArticleTo> articles = getDetails(publishTime);
			System.out.println(name + "详情文章数：" + articles.size());
			Template tmpl = cfg.getTemplate("detail.ftl");
			StringWriter out = null;
			Map<String, Object> articleMap = new HashMap<String, Object>();
			for(ArticleTo arti : articles) {
				try {
					String code = (null == arti.getTypeCode() || arti.getTypeCode().length()==0) ? arti.getCategoryCode() : arti.getTypeCode();
					List<ArticleTo> articleList = articlesMap.get(code);
					if(null == articleList || articleList.size() == 0) {
						articleList = new ArrayList<ArticleTo>();
						articleList = getArticlesByCategoryId("category_id", arti.getCategoryId(), 12, publishTime, false);
						articlesMap.put(code, articleList);
					}

					articleMap.put("keywords", cats.get(arti.getCategoryId()).getKeywords());
					articleMap.put("descMeta", cats.get(arti.getCategoryId()).getDescMeta());
//					articleMap.put("summary", cats.get(arti.getCategoryId()).getSummary());

					articleMap.put("article", arti);
					articleMap.put("recomms", articleList);
					out = new StringWriter();
					tmpl.process(articleMap, out);
					String catCode = arti.getCategoryCode();
					// 生成文件
					createFile(out.toString(), catCode + "/d" + arti.getId() +".html");
					if(outFlag == 3) return;
				} catch (Exception e) {
					e.printStackTrace();
				} finally {
					try {
						if (null != out)
							out.close();
					} catch (Exception e) {
						e.printStackTrace();
					}
				}
			}
		} catch (IOException e) {
			e.printStackTrace();
		}
	}
	
	public static void createList(Configuration cfg, String publishTime) {
		try {
			if(null == cats || cats.isEmpty()) getTypes();
//			List<Category> categories = getTypes();
			int pageSize = 10;
			List<ArticleTo> allHopArtisImgs = getArticlesImgByCategoryId(null, 0, 280, 1.68, 0.4, 1, true);
			String ids = getIds(null, allHopArtisImgs);
			// 获取全频道最热文章
			List<ArticleTo> allHopArtis= getArticles(ids, 0, 10, true, true, false);
			ids = getIds(ids, allHopArtis);
			// 记录各个类别下最热文章
			Map<Long, List<ArticleTo>> catHopArtisMaps = new HashMap<Long, List<ArticleTo>>();
			for(long cid : cats.keySet()) {
//			for(Category category : categories) {
				Category category = cats.get(cid);
				Map<String, Object> articleMap = new HashMap<String, Object>();
				int count = 0 != category.getParentId() ? getArticlesCountByTypeId(cid, publishTime) : getArticlesCountByCategoryId(cid, publishTime);
				List<ArticleTo> articleList = null;
				if(count == 0) {
					continue;
				} else {
					if(0 != category.getParentId()) {
						// 二级分类不生成列表
						continue;
					} else {
						articleList = getArticlesByCategoryId("category_id", cid, 100000, null, true);
						// 获取本版最热的数据 
						catHopArtisMaps.put(cid, getArticles(ids, cid, 10, true, true, false));
					}
				}
				
				articleMap.put("allHopArtis", allHopArtis);
				articleMap.put("catHotArtis", catHopArtisMaps.get(cid));
				articleMap.put("allHotArtisImg", allHopArtisImgs);
				
				for(int start = 0; (start) * pageSize < articleList.size(); start++) {
					articleMap.put("articles", articleList.subList(start * pageSize, ((start + 1) * pageSize >= articleList.size() ? articleList.size() : (start + 1) * pageSize)));
					if(null == category.getParentId() || category.getParentId() == 0) {
						articleMap.put("categoryName", category.getName());
						articleMap.put("categoryCode", category.getCode());
					} else {
						articleMap.put("categoryName", cats.get(category.getParentId().longValue()).getName());
						articleMap.put("categoryCode", cats.get(category.getParentId().longValue()).getCode());
						articleMap.put("typeName", category.getName());
						articleMap.put("typeCode", category.getCode());
					}
					
					articleMap.put("keywords", category.getKeywords());
					articleMap.put("descMeta", category.getDescMeta());
					articleMap.put("summary", category.getSummary());
					articleMap.put("count", articleList.size());
					articleMap.put("pageSize", pageSize);
					articleMap.put("pageNo", start);
					
					Template tmpl = cfg.getTemplate("list.ftl");
					StringWriter out = null;
					try {
						out = new StringWriter();
						tmpl.process(articleMap, out);
						if(start==0 && category.getParentId().equals(0)) {
							createFile(out.toString(), category.getCode() + "/index.html");
						}
						if(outFlag == 3) return;
						createFile(out.toString(), category.getCode() + "/list_"+ category.getCode() + "_" + (start+1) +".html");
					} catch (Exception e) {
						e.printStackTrace();
					} finally {
						try {
							if (null != out)
								out.close();
						} catch (Exception e) {
							e.printStackTrace();
						}
					}
				}
			}
		} catch (IOException e) {
			e.printStackTrace();
		}
	}
	
	/**
	 * 获取图秀的6张图
	 * @return
	 */
	public static List<ArticleTo> getImages(String ids) {
		List<ArticleTo> imgArticles1 = new ArrayList<ArticleTo>();
		List<ArticleTo> imgArticles2 = new ArrayList<ArticleTo>();
		List<ArticleTo> imgArticles3 = new ArrayList<ArticleTo>();
		List<ArticleTo> imgArticles4 = new ArrayList<ArticleTo>();
		// 计数，当达到6时就不在取图片
		int imgNum = 0;
		List<ArticleTo> articles = new ArrayList<ArticleTo>();
		try {
			Connection connection = DbUtil.getQgshConn();
			Statement statement = connection.createStatement();
			String sql = "SELECT a.id,a.title,a.publish_time,a.category_id,a.category_code,a.type_id,a.type_code,a.content,a.summary FROM qg_article_1nsh a where "
					+ " state=1 and hot =1 and create_time < '"+HtmlUtil.getNow(12)+"' and id not in (" + ids 
					+ ") and content like '%<img%' order by a.create_time desc  limit 200";
			ResultSet rs = statement.executeQuery(sql);
			while (rs.next()) {
				ArticleTo articleTo = new ArticleTo();
				articleTo.setId(rs.getLong("id"));
				articleTo.setTitle(rs.getString("title"));
				articleTo.setPublishTime(rs.getDate("publish_time"));
				articleTo.setCategoryId(rs.getLong("category_id"));
				articleTo.setCategoryCode(rs.getString("category_code"));
				articleTo.setTypeId(rs.getLong("type_id"));
				String typeCode = rs.getString("type_code");
				articleTo.setTypeCode(isEmpty(typeCode) ? articleTo.getCategoryCode() : typeCode);
				String content = rs.getString("content");
				// 提取src值
				List<String> imgSrcs = ImageUtil.getImgSrc(content);
				String summary = rs.getString("summary");
				if(null == summary || summary.length() ==0) {
					content = HtmlUtil.delHTMLTag(content);
					summary = content.substring(0, Math.min(150, content.length()));
				}
				articleTo.setSummary(summary);
				if(imgSrcs.isEmpty()) {
					continue;
				} else if(imgNum == 6) {
					break;
				} else {
					for(String imgSrc : imgSrcs) {
//						String localSrc = oldImgMap.get(imgSrc);
						if(isEmpty(imgSrc) || !imgSrc.startsWith(remoteImgDir)) {
							continue;
						}
						try {
							double imgPr = getPr(imgSrc, articleTo);
							/**
								1、1.38  400/290
								 2-3、1.38  193 140
								 4-5、1.4 300 415
								 6、0.79  350 445
							 */
							// TODO 替换为线上图片地址localSrc
					        if(imgArticles1.size() < 1 && rangeInDefined(imgPr, 1.38-0.3, 1.38+0.3) && articleTo.getImgWidth() >= 300) {
					        	// 此种类型的图片取1张
					        	articleTo.setMainImg(imgSrc);
					        	imgArticles1.add(articleTo);
					        	imgNum++;
					        	break;
					        } else if(imgArticles2.size() < 2 && rangeInDefined(imgPr, 1.38-0.4, 1.38+0.4) && articleTo.getImgWidth() >= 150) {
					        	// 此种类型的图片取2张
					        	articleTo.setMainImg(imgSrc);
					        	imgArticles2.add(articleTo);
					        	imgNum++;
					        	break;
					        } else if(imgArticles3.size() < 2 && rangeInDefined(imgPr, 1.4-0.4, 1.4+0.4) && articleTo.getImgWidth() >= 250) {
					        	// 此种类型的图片取2张
					        	articleTo.setMainImg(imgSrc);
					        	imgArticles3.add(articleTo);
					        	imgNum++;
					        	break;
					        } else if(imgArticles4.size() < 1 && rangeInDefined(imgPr, 0.79-0.2, 0.79+0.2) && articleTo.getImgWidth() >= 280) {
					        	// 此种类型的图片取1张
					        	articleTo.setMainImg(imgSrc);
					        	imgArticles4.add(articleTo);
					        	imgNum++;
					        	break;
					        }
						} catch (Exception e) {
							e.printStackTrace();
							continue;
						}
					}
				}
				articles.add(articleTo);
			}
			rs.close();
			statement.close();
			// 如果符合的尺寸不够，则滥竽充数
			/**
			 Map<Double, Integer> flucs = new HashMap<Double, Integer>();
				1、1.38  400/290
				 2-3、1.38  193 140
				 4-5、1.4 300 415
				 6、0.79  350 445
			 */
			if(imgArticles1.size() < 1) {
				imgArticles1.addAll(getImgByWidth(articles, 300, 1));
			}
			if(imgArticles2.size() < 2) {
				imgArticles2.addAll(getImgByWidth(articles, 150, 2 - imgArticles2.size()));
			}
			if(imgArticles3.size() < 2) {
				imgArticles3.addAll(getImgByWidth(articles, 250, 2 - imgArticles3.size()));
			}
			if(imgArticles4.size() < 1) {
				imgArticles4.addAll(getImgByWidth(articles, 280, 1));
			} 
		} catch (Exception e) {
			System.out.println(name + "获取数据发生异常：" + e.getMessage());
			e.printStackTrace();
			// 关闭连接
			DbUtil.closeConn();
		}

		imgArticles1.addAll(imgArticles2);
		imgArticles1.addAll(imgArticles3);
		imgArticles1.addAll(imgArticles4);
		return imgArticles1;
	}

	/**
	 * 获取带图片的非热门文章列表
	 * @param field	条件过滤字段
	 * @param categoryId	类别
	 * @param w	最小宽度
	 * @param pr	宽高比
	 * @param fluc	图片尺寸的宽高浮动范围
	 * @param imgNum	图片数
	 * @return
	 */
	public static List<ArticleTo> getArticlesImgByCategoryId(String field, int categoryId, int w, double pr, double fluc, int imgNum) {
		return getArticlesImgByCategoryId(null, categoryId, w, pr, fluc, imgNum, false);
	}
	
	/**
	 * 获取带图片的文章列表
	 * @param field	条件过滤字段
	 * @param categoryId	类别
	 * @param w	最小宽度
	 * @param pr	宽高比
	 * @param fluc	图片尺寸的宽高浮动范围
	 * @param imgNum	图片数
	 * @param hot	是否只获取热门数据
	 * @return
	 */
	public static List<ArticleTo> getArticlesImgByCategoryId(String ids, int categoryId, int w, double pr, double fluc, int imgNum, boolean hot) {
		String sql = "SELECT a.id,a.title,a.publish_time,a.category_id,a.category_code,a.type_id,a.type_code,a.content,a.summary FROM qg_article_1nsh a where "
				+ " create_time < '"+HtmlUtil.getNow(12)+"' and state=1 "+(0==categoryId ? "" : " and category_id=" + categoryId) + (hot ? " and hot =1 " : "")
				+ (isEmpty(ids) || ids.replaceAll(",", "").length() == 0 ? "" : " and id not in (" +ids + ") ")
				+ " and content like '%<img%' and length(content) >1000 order by a.create_time desc limit 100";
		return getArtiHasImg(sql, w, pr, fluc, imgNum);
	}
	/**
	 * 获取含图片的文章
	 * @param sql	查询的SQL语句
	 * @param w	最小宽度
	 * @param pr	宽高比
	 * @param fluc	图片尺寸的宽高浮动范围
	 * @param imgNum	图片数
	 * @return
	 */
	private static List<ArticleTo> getArtiHasImg(String sql, int w, double pr, double fluc, int imgNum) {
		List<ArticleTo> articles = new ArrayList<ArticleTo>();
		List<ArticleTo> imgArticles = new ArrayList<ArticleTo>();
		try {
			Connection connection = DbUtil.getQgshConn();
			Statement statement = connection.createStatement();
			ResultSet rs = statement.executeQuery(sql);
			while (rs.next()) {
				ArticleTo articleTo = new ArticleTo();
				articleTo.setId(rs.getLong("id"));
				articleTo.setTitle(rs.getString("title"));
				articleTo.setPublishTime(rs.getDate("publish_time"));
				articleTo.setCategoryId(rs.getLong("category_id"));
				articleTo.setCategoryCode(rs.getString("category_code"));
				articleTo.setTypeId(rs.getLong("type_id"));
				String typeCode = rs.getString("type_code");
				articleTo.setTypeCode(isEmpty(typeCode) ? articleTo.getCategoryCode() : typeCode);
				String content = rs.getString("content");
				articleTo = getImgFromContent(content, articleTo, pr, fluc, w);
				// 没有图片
				if(articleTo.getImgWidth() == 0) {
					continue;
				}
				// 有主图
				if(!HtmlUtil.isEmpty(articleTo.getMainImg())) {
					imgArticles.add(articleTo);
					if(imgArticles.size() == imgNum) break;
					continue;
				}
				
				String summary = rs.getString("summary");
				if(null != summary && summary.length() > 5) {
					articleTo.setSummary(summary);
				}
				articles.add(articleTo);
			}
			rs.close();
			statement.close();
			// 如果尺寸不够，则滥竽充数
			if(imgArticles.size() < imgNum && articles.size() >= imgNum - imgArticles.size()) {
				for(ArticleTo arti : articles) {
					if(arti.getImgWidth() >= w) {
						imgArticles.add(arti);
					}
				}
			} 
		} catch (Exception e) {
			System.out.println(name + "获取数据发生异常：" + e.getMessage());
			e.printStackTrace();
			// 关闭连接
			DbUtil.closeConn();
		}
		return imgArticles;
	}
	/**
	 * 获取图片尺寸比率
	 * @param imgSrc
	 * @return
	 * @throws Exception
	 */
	private static double getPr(String imgSrc, ArticleTo articleTo) throws Exception {
		// 替换URL，从本地获取图片
		imgSrc = imgSrc.replace(remoteImgDir, localImgDir);
//		imgSrc = imgSrc.replace("http://www.wine22.com/images/", localImgDir).replace("http://www.g2room.com/images/", localImgDir);
		File f = new File(imgSrc);
		double pr = 0;
	    try {
	    	BufferedImage sourceImg =ImageIO.read(new FileInputStream(f));
	    	if(null != sourceImg) {
	    		articleTo.setImgWidth(sourceImg.getWidth());
	    		pr = Double.valueOf(articleTo.getImgWidth()) / sourceImg.getHeight();
	    	}
	    } catch (Exception e) {
	    	e.printStackTrace();
	    }
        return pr;
	}
	
	/**
	 * 根据类型查询数据，根据创建时间排序
	 * @param field	查询字段
	 * @param categoryId	类别ID
	 * @param limit	获取文章数量
	 * @param publishTime	发布时间
	 * @param content	是否获取内容
	 * @return
	 */
	public static List<ArticleTo> getArticlesByCategoryId(String field, long cid, int limit, String publishTime, boolean cont) {
		List<ArticleTo> articles = new ArrayList<ArticleTo>();
		try {
			// 处理按大类搜索时，小类设置为空
			String sqlSub = "";
			if(!isEmpty(field)) {
				sqlSub = " and "+field+" =" + cid ;
			}
			Connection connection = DbUtil.getQgshConn();
			Statement statement = connection.createStatement();
			String sql = "SELECT a.id,a.title,a.publish_time,a.category_id,a.category_code,a.type_id,a.thumb,a.type_code"+( cont ? ",a.content,a.summary" : "")+" FROM qg_article_1nsh a "
					+ "where " + (isEmpty(publishTime) ? "" : "create_time>'" + publishTime + "' and ") 
					+ " create_time < '"+HtmlUtil.getNow(12)+"' and state=1 "+ sqlSub
					+ " order by a.create_time desc  limit " + limit;
			ResultSet rs = statement.executeQuery(sql);
			while (rs.next()) {
				ArticleTo articleTo = new ArticleTo();
				articleTo.setId(rs.getLong("id"));
				articleTo.setTitle(rs.getString("title"));
				articleTo.setPublishTime(rs.getDate("publish_time"));
				articleTo.setCategoryId(rs.getLong("category_id"));
				articleTo.setCategoryCode(rs.getString("category_code"));
				articleTo.setTypeId(rs.getLong("type_id"));
				String typeCode = rs.getString("type_code");
				articleTo.setTypeCode(isEmpty(typeCode) ? articleTo.getCategoryCode() : typeCode);
				articleTo.setThumb(rs.getString("thumb"));
				if(cont) {
					articleTo.setSummary(rs.getString("summary"));
					if(null == articleTo.getSummary() || articleTo.getSummary().length() <5) {
						articleTo.setSummary(handleContent(rs.getString("content")));
					}
				}
				articles.add(articleTo);
			}
			rs.close();
			statement.close();
		} catch (Exception e) {
			System.out.println(name + "获取数据发生异常：" + e.getMessage());
			e.printStackTrace();
			// 关闭连接
			DbUtil.closeConn();
		}
		return articles;
	}
	/**
	 * 从内容中提取出符合要求的图片
	 * @param content
	 * @param articleTo
	 * @param pr
	 * @param fluc
	 * @param w
	 * @return
	 */
	private static ArticleTo getImgFromContent(String content, ArticleTo articleTo, double pr, double fluc, int w) {
		// 提取src值
		List<String> imgSrcs = ImageUtil.getImgSrc(content);
		articleTo.setSummary(Jsoup.parse(content).text());
		if(imgSrcs.isEmpty()) return articleTo;
		
		for(String imgSrc : imgSrcs) {
			if(isEmpty(imgSrc) || !imgSrc.startsWith(remoteImgDir)) {
				continue;
			}
			try {
				double imgPr = getPr(imgSrc, articleTo);
		        if(pr > 0 && rangeInDefined(imgPr, pr-fluc, pr+fluc) && articleTo.getImgWidth() >= w) {
		        	articleTo.setMainImg(imgSrc);
		        	break;
		        } else if(articleTo.getImgWidth() >= w){
		        	articleTo.setMainImg(imgSrc);
		        }
			} catch (Exception e) {
				e.printStackTrace();
				continue;
			}
		}
		return articleTo;
	}

	/**
	 * 根据类别ID获取最热数据，取最新的n条数据
	 * @param ids	待排除的id
	 * @param cid	类别id，为0取全部类型
	 * @param limit	获取数据条数
	 * @param hot	获取热门数据
	 * @param thumb	获取含缩略图数据
	 * @return
	 */
	public static List<ArticleTo> getArticles(String ids, long cid, int limit, 
			boolean hot, boolean thumb, boolean content) {
		List<ArticleTo> articles = new ArrayList<ArticleTo>();
		try {
			Connection connection = DbUtil.getQgshConn();
			Statement statement = connection.createStatement();
			String sql = "SELECT a.id,a.title,a.publish_time,a.category_id,a.category_code,a.category_name,a.type_id,a.type_code,a.thumb,a.summary"+(content ? ",content " : " ")+" FROM qg_article_1nsh a where state=1 "
					+ " and create_time < '"+HtmlUtil.getNow(12)+"' "
					+ (hot ? " and hot=1" : "" ) + (cid==0 ? "" : " and category_id = " +cid) 
					+ (isEmpty(ids) || ids.replaceAll(",", "").length() == 0 ? "" : " and id not in (" +ids + ") ") 
					+ (thumb ? " and thumb is not null" : "") 
					+ "  order by a.create_time desc  limit " + limit;
			ResultSet rs = statement.executeQuery(sql);
			while (rs.next()) {
				ArticleTo articleTo = new ArticleTo();
				articleTo.setId(rs.getLong("id"));
				articleTo.setTitle(rs.getString("title"));
				articleTo.setPublishTime(rs.getDate("publish_time"));
				articleTo.setCategoryId(rs.getLong("category_id"));
				articleTo.setCategoryCode(rs.getString("category_code"));
				articleTo.setCategoryName(rs.getString("category_name"));
				articleTo.setTypeId(rs.getLong("type_id"));
				articleTo.setThumb(rs.getString("thumb"));
				String summary = rs.getString("summary");
				if((null==summary || summary.length() == 0) && content) {
					summary = rs.getString("content");
					if(summary.length()> 0) {
						summary = HtmlUtil.delHTMLTag(summary).trim().replaceAll(" ", "").replaceAll(" ", "");
						summary = summary.substring(0, Math.min(150, summary.length())).replaceAll("　", "");
					}
				}
				articleTo.setSummary(summary);
				String typeCode = rs.getString("type_code");
				articleTo.setTypeCode(isEmpty(typeCode) ? articleTo.getCategoryCode() : typeCode);
				articles.add(articleTo);
			}
			rs.close();
			statement.close();
		} catch (Exception e) {
			e.printStackTrace();
			// 关闭连接
			DbUtil.closeConn();
		}
		return articles;
	}


	public static int getArticlesCountByCategoryId(long categoryId, String publishTime) {
		return getArticlesCountByCategoryId("category_id", categoryId, publishTime);
	}
	
	public static int getArticlesCountByTypeId(long typeId, String publishTime) {
		return getArticlesCountByCategoryId("type_id", typeId, publishTime);
	}
	public static int getArticlesCountByCategoryId(String field, long categoryId, String publishTime) {
		int count = 0;
		try {
			Connection connection = DbUtil.getQgshConn();
			Statement statement = connection.createStatement();
			String sql = "SELECT count(*) as count FROM qg_article_1nsh a "
					+ "where " + (isEmpty(publishTime) ? "" : "create_time>'" + publishTime + "' and ") 
					+ " create_time < '"+HtmlUtil.getNow(12)+"' and state=1 and "+field+"=" + categoryId;
			ResultSet rs = statement.executeQuery(sql);
			while (rs.next()) {
				count = rs.getInt("count");
			}
			rs.close();
			statement.close();
		} catch (Exception e) {
			System.out.println(name + "获取统计数据发生异常：" + e.getMessage());
			e.printStackTrace();
			// 关闭连接
			DbUtil.closeConn();
		}
		return count;
	}
	
	public static List<ArticleTo> getDetails(String publishTime) {
		String sql = "select id,category_id, category_code, type_code,category_name, type_name,type_id,title,summary, content,publish_time,tag,source,source_url from qg_article_1nsh "
				+ "where create_time>'" + publishTime + "' and create_time < '"+HtmlUtil.getNow(12)+"' and state=1";
		return getDetailsData(sql, true,  true, false);
	}
	public static List<ArticleTo> getLatestArtis(int limit) {
		String sql = "select id,category_id, category_code, type_code,category_name, type_name,type_id,title,thumb, publish_time from qg_article_1nsh a where state=1 and create_time < '"+HtmlUtil.getNow(12)+"' and a.id in (select aa.id from (select b.id from qg_article_1nsh b where create_time < '"+HtmlUtil.getNow(12)+"' order by b.create_time desc limit 50) as aa )  order by rand() desc limit " + limit;
		return getDetailsData(sql, false, false, false);
	}
	
	public static List<ArticleTo> getDetailsData(String sql, boolean source, boolean content, boolean thumb) {
		List<ArticleTo> articles = new ArrayList<ArticleTo>();
		try {
			Connection connection = DbUtil.getQgshConn();
			Statement statement = connection.createStatement();
			ResultSet rs = statement.executeQuery(sql);
			while (rs.next()) {
				ArticleTo articleTo = new ArticleTo();
				articleTo.setId(rs.getLong("id"));
				articleTo.setTitle(rs.getString("title"));
				if(thumb) articleTo.setThumb(rs.getString("thumb"));
				if(content) {
					articleTo.setContent(rs.getString("content"));
					articleTo.setSummary(rs.getString("summary"));
				}
				articleTo.setPublishTime(rs.getDate("publish_time"));
				articleTo.setCategoryCode(rs.getString("category_code"));
				articleTo.setCategoryName(rs.getString("category_name"));
				articleTo.setTypeId(rs.getLong("type_id"));
				articleTo.setCategoryId(rs.getLong("category_id"));
				String typeCode = rs.getString("type_code");
				articleTo.setTypeCode(isEmpty(typeCode) ? articleTo.getCategoryCode() : typeCode);
				String typeName = rs.getString("type_name");
				articleTo.setTypeName(isEmpty(typeName) ? rs.getString("category_name") : typeName);
				if(source) {
					articleTo.setTag(rs.getString("tag"));
					articleTo.setSource(rs.getString("source"));
					articleTo.setSourceUrl(rs.getString("source_url"));
				}
				
				articles.add(articleTo);
			}
			rs.close();
			statement.close();
//			connection.close();
		} catch (Exception e) {
			e.printStackTrace();
			// 关闭连接
			DbUtil.closeConn();
		}
		return articles;
	}
	
	public static Map<Long, Category> getTypes() {
		try {
			Connection connection = DbUtil.getQgshConn();
			Statement statement = connection.createStatement();
			String sql = "select * from qg_category order by parent_id asc";
			ResultSet rs = statement.executeQuery(sql);
			while(rs.next()) {
				Category category = new Category();
				long id = rs.getLong("id");
				category.setId(id);
				category.setCode(rs.getString("code"));
				category.setName(rs.getString("name"));
				category.setSummary(rs.getString("summary"));
				category.setKeywords(rs.getString("keywords"));
				category.setDescMeta(rs.getString("descMeta"));
				category.setParentId(rs.getInt("parent_id"));
				cats.put(id, category);
			}
			rs.close();
			statement.close();
		} catch(Exception e) {
			e.printStackTrace();
			// 关闭连接
			DbUtil.closeConn();
		}
		return cats;
	}

	public static void main(String[] args) throws Exception {
		createHtml();
	}
	
	public static boolean isEmpty(String str) {
		return null == str || str.length() == 0;
	}
	
	public static void createFile(String content, String fileName) throws Exception {
		String filePath = baseHtmlPath + fileName;
        //得到写文件路径
        File file = new File(filePath.substring(0, filePath.lastIndexOf("/")));
        //如果目录不存在，则创建目录
        if(!file.exists())  file.mkdirs();
        //写文件
        OutputStreamWriter out = new OutputStreamWriter(new FileOutputStream(new File(filePath)),"utf-8");
//        publishHtmlPath = HtmlUtil.getPath(baseHtmlPath);
//        String tempFilePath =  publishHtmlPath + fileName;
//        //得到写文件路径
//        File file1 = new File(tempFilePath.substring(0, tempFilePath.lastIndexOf("/")));
//        //如果目录不存在，则创建目录
//        if(!file1.exists())  file1.mkdirs();
//        //写如待上传的目录文件
//        OutputStreamWriter out1 = new OutputStreamWriter(new FileOutputStream(new File(tempFilePath)),"utf-8");
//        out1.write(content);
//        out1.flush();
//        out1.close();
        
        out.write(content);
        out.flush();
        out.close();
    }
	/**
	 * 判断是否在范围内，在则返回true
	 * @param current	当前数据
	 * @param min 最小
	 * @param max	最大
	 * @return
	 */
	public static boolean rangeInDefined(double current, double min, double max) {
		return Math.max(min, current) == Math.min(current, max);
	}
	
	public static String getIds(String idStr, List<ArticleTo> artis) {
		// 获取IDS
		StringBuffer ids = new StringBuffer();
		if(null != idStr && idStr.length() > 0) ids.append(idStr);
		for(ArticleTo arti : artis) {
			if(ids.length() > 0) ids.append(",");
			ids.append(arti.getId());
		}
		return ids.toString();
	}
	
	private static List<ArticleTo> getImgByWidth(List<ArticleTo> articles, int w, int num) {
		List<ArticleTo> artis = new ArrayList<ArticleTo>();
		for(ArticleTo arti : articles) {
			if(arti.getImgWidth() >= w) {
				artis.add(arti);
				num--;
			}
			if(num == 1) break;
		}
		return artis;
	}

	/**
	 * 获取所有图片的content里面的地址
	 * @return
	 */
	public static Map<String, String> getOldImgs(int type) {
		Map<String, String> map = new HashMap<String, String>();
		try {
			Connection connection = DbUtil.getQgshConn();
			Statement statement = connection.createStatement();
			String sql="select id,oldSrc,localSrc from qg_images where type=" + type;
			ResultSet rs = statement.executeQuery(sql);
			while (rs.next()) {
				if(type == 0) {
					map.put(rs.getString("oldSrc"), rs.getString("localSrc"));
				} else {
					map.put(rs.getString("localSrc"), rs.getString("oldSrc"));
				}
			}
			rs.close();
			statement.close();
		} catch (Exception e) {
			e.printStackTrace();
			// 关闭连接
			DbUtil.closeConn();
		}
		return map;
	}
	
	public static String handleContent(String content) {
		content = HtmlUtil.delHTMLTag(content);
		if(null != content && content.length() > 0) {
			if(content.length()> 0) {
				content = content.substring(0, Math.min(150, content.length())).replaceAll("　", "").replaceAll(" ", "").replaceAll(" ", "");
			}
		}
		return content;
	}
}
